# -*- coding: utf-8 -*-
"""
Spyder Editor

This is a temporary script file.
"""
import numpy as np
import duckdb
import pandas as pd

db = duckdb.connect('~/myduck')
## 门诊补助
"""
mz = db.sql("SELECT * FROM jssj WHERE 医疗类别='普通门诊' AND 是否参与计算= 1 AND 是否补助=TRUE").df()

mz_zz = mz[mz['人员类别'] =='在职'].groupby('身份证号',as_index=False).agg({'医疗费总额':'sum','统筹支出':'sum','全自费金额':'sum','超限价自费费用':'sum'})
mz_tx = mz[mz['人员类别']=='退休'].groupby('身份证号',as_index=False).agg({'医疗费总额':'sum','统筹支出':'sum','全自费金额':'sum','超限价自费费用':'sum'})

mz_zz['门诊自付'] = mz_zz['医疗费总额'] - mz_zz['统筹支出'] - mz_zz['全自费金额'] - mz_zz['超限价自费费用']
mz_tx['门诊自付'] = mz_tx['医疗费总额'] - mz_tx['统筹支出'] - mz_tx['全自费金额'] - mz_tx['超限价自费费用']
mz_zz['门诊起付线'] = 1000
mz_tx['门诊起付线'] = 1000

mz_zz['门诊补助'] = ( mz_zz['门诊自付'] - mz_zz['门诊起付线'] ) * 0.8
mz_zz.loc[mz_zz['门诊补助']<0,'门诊补助'] = 0
mz_zz.loc[mz_zz['门诊补助']>1800,'门诊补助'] = 1800

mz_tx['门诊补助'] = ( mz_tx['门诊自付'] - mz_tx['门诊起付线'] ) * 0.8
mz_tx.loc[mz_tx['门诊补助']<0,'门诊补助'] = 0
mz_tx.loc[mz_tx['门诊补助']>2300,'门诊补助'] = 2300

mz_zz_round = mz_zz.round(decimals=2)
mz_tx_round = mz_tx.round(decimals=2)

db.sql("SELECT * FROM mz_zz_round UNION SELECT * FROM mz_tx_round").show()
"""
## 住院补助
zy_sql = """
SELECT
	jssj.*,
	ifnull(透析人员费用.补充支付,0)  as 透析费用
FROM
	jssj
left join 透析人员费用
on
	(jssj.身份证号 = 透析人员费用.身份证号)
WHERE
	jssj.是否补助 = true
	AND jssj.是否参与计算 = 1
	AND jssj.医疗类别 IN ('特药门诊', '门诊慢病', '急诊住院', '普通住院', '异地住院', '转外诊治住院')
"""
zy = db.sql(zy_sql).df()


zy_zz = zy[zy['人员类别'] =='在职'].groupby('身份证号',as_index=False).agg({
	'医疗费总额':'sum',
	'统筹支出':'sum',
	'全自费金额':'sum',
	'超限价自费费用':'sum',
	'大额支出':'sum',
	'透析费用':'sum'
})
zy_tx = zy[zy['人员类别'] =='退休'].groupby('身份证号',as_index=False).agg({
	'医疗费总额':'sum',
	'统筹支出':'sum',
	'全自费金额':'sum',
	'超限价自费费用':'sum',
	'大额支出':'sum',
	'透析费用':'sum'
})

dae_fdx = 400000
zy_zz['住院起付线'] = 2000
zy_tx['住院起付线'] = 2000

# 大额未封顶 
zy_zz['大额封顶线'] = dae_fdx
zy_tx['大额封顶线'] = dae_fdx

zy_zz_wfd = zy_zz[ zy_zz['大额支出'] - dae_fdx <= 0 ]
zy_tx_wfd = zy_tx[ zy_tx['大额支出'] - dae_fdx <= 0 ]
zy_zz_wfd['住院自付'] =  zy_zz_wfd['医疗费总额'] - zy_zz_wfd['统筹支出'] - zy_zz_wfd['全自费金额'] - zy_zz_wfd['超限价自费费用'] - zy_zz_wfd['大额支出'] - zy_zz_wfd['透析费用']
zy_tx_wfd['住院自付'] =  zy_tx_wfd['医疗费总额'] - zy_tx_wfd['统筹支出'] - zy_tx_wfd['全自费金额'] - zy_tx_wfd['超限价自费费用'] - zy_tx_wfd['大额支出'] - zy_tx_wfd['透析费用']
zy_zz_wfd['住院补助'] = ( zy_zz_wfd['住院自付'] - zy_zz_wfd['住院起付线'] ) * 0.85
zy_tx_wfd['住院补助'] = ( zy_tx_wfd['住院自付'] - zy_tx_wfd['住院起付线'] ) * 0.9
zy_zz_wfd.loc[zy_zz_wfd['住院补助']<0,'住院补助'] = 0
zy_tx_wfd.loc[zy_tx_wfd['住院补助']<0,'住院补助'] = 0
zy_zz_wfd_round=zy_zz_wfd.round(decimals=2)
zy_tx_wfd_round=zy_tx_wfd.round(decimals=2)

#db.sql("SELECT * FROM zy_zz_wfd_round").show()

# 大额封顶
zy_zz_fd = zy_zz[ zy_zz['大额支出'] - dae_fdx > 0 ]
zy_tx_fd = zy_tx[ zy_tx['大额支出'] - dae_fdx > 0 ]
zzfd_cnt = zy_zz_fd['身份证号'].count()
txfd_cnt = zy_tx_fd['身份证号'].count()

zyzf = []
dezf = 0.00
if zzfd_cnt > 0:
	for sfz in zy_zz_fd['身份证号']:
		print(sfz)
		zy_zz_jssj = zy.loc[ zy['身份证号']== sfz].sort_values(by='结算时间', ascending=True)
		for i in range(len(zy_zz_jssj)):
			dezf += zy_zz_jssj.iloc[i]['大额支出']
			if dezf > dae_fdx:
				last_zf = zy_zz_jssj.iloc[i]['大额支出'] / 0.9 * 0.1 + zy_zz_jssj.iloc[i]['先行自付金额']
				zyzf.append(last_zf)
			else:
				zyzf.append( zy_zz_jssj.iloc[i]['医疗费总额'] - zy_zz_jssj.iloc[i]['统筹支出'] - zy_zz_jssj.iloc[i]['全自费金额'] - zy_zz_jssj.iloc[i]['超限价自费费用'] - zy_zz_jssj.iloc[i]['大额支出'] - zy_zz_jssj.iloc[i]['透析费用'])
		zf = sum(zyzf)
		print(zf)


## 大病补助
